﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace QuanLyKhachSan.DataAccess
{
    class ConnectData
    {
        private SqlConnection conn;
        private SqlDataAdapter dataAp;
        private DataTable dataTable;
        //tạo construct gọi kết nối
        public ConnectData() {
            Connect();
        }

        public void Connect() {
            string strConn = @"Data Source=DHXD-PC\SQLEXPRESS;Initial Catalog=QuanLyKhachSan;User Id=sa;Password=123456";
            //try
            //{
                conn = new SqlConnection(strConn);
                conn.Open();
                conn.Close();
            //}
            //catch (Exception ex)
            //{
                
            //    MessageBox.Show("Lỗi:"+ex.Message);
            //}
        }

        public SqlConnection Connect1()
        {
            SqlConnection strConn1 = new  SqlConnection(@"Data Source=DHXD-PC\SQLEXPRESS;Initial Catalog=QuanLyKhachSan;User Id=sa;Password=123456");
            return strConn1;
        }
        //Hàm lấy dữ liệu từ dataTable từ câu lệnh insert, delete, update
        public DataTable GetDataTable(string sql) {
            dataAp = new SqlDataAdapter(sql, conn);
            dataTable = new DataTable();
            dataAp.Fill(dataTable);
            return dataTable;
        }

        //Hàm thực hiện câu lệnh sql
        public bool ExeCuteQuery(string sql) {
            int numRecordEffect = 0;
            try
            {
                if (conn.State==ConnectionState.Closed)
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    numRecordEffect = cmd.ExecuteNonQuery();
                }
                if (conn.State==ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                
                MessageBox.Show("Lỗi: "+ex.Message);
            }
            if (numRecordEffect>0)
            {
                return true;
            }
            return false;
        }

        public string GetLastID(string namTable, string namFiled) {
            string sql = "SELECT TOP 1 " + namFiled + " FROM " + namTable + " ORDER BY " + namFiled + " DESC";
            GetDataTable(sql);
            return dataTable.Rows[0][namFiled].ToString();
        }

        public bool CheckExistValue(string nameTable, string nameFiled, string value) {
            string sql = "select * from "+nameTable+" where "+nameFiled+" = '"+value+"'";
            //đếm dòng trả về nếu > 0 tức là tồn tại value
            GetDataTable(sql);
            if (dataTable.Rows.Count>0)
            {
                return true;
            }
            return false;
        }
        public SqlDataReader LayAnh(string sql, string[] name, object[] value, int Nparameter)
        {
            SqlCommand command = new SqlCommand(sql, conn);
            command.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < Nparameter; i++)
            {
                command.Parameters.AddWithValue(name[i], value[i]);
            }

            SqlDataReader reader = command.ExecuteReader();
            return reader;
        }
    }
}
